package com.murong.ai.coder.domain.database.sqlhandler;

import com.murong.ai.coder.entity.TableEntity;
import com.murong.ai.coder.infrastructure.util.MrStringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class OracleSqlHandler {

    public static TableEntity handlerCreateTable(String sql) {
        TableEntity entity = new TableEntity();
        // 1. 表名
        Pattern tableNamePattern = Pattern.compile("create table ([a-zA-Z0-9_]+)", Pattern.CASE_INSENSITIVE);
        Matcher tableNameMatcher = tableNamePattern.matcher(sql);
        if (tableNameMatcher.find()) {
            String tableName = tableNameMatcher.group(1);
            entity.setTableNameSnake(tableName);
            // 驼峰名简单转化
            entity.setTableNameCamel(MrStringUtils.toCamel(tableName));
        }
        // 2. 字段
        Pattern fieldPattern = Pattern.compile("\\((.*?)\\)\\s*(;|$)", Pattern.DOTALL);
        Matcher fieldMatcher = fieldPattern.matcher(sql);
        List<TableEntity.Field> fields = new ArrayList<>();
        if (fieldMatcher.find()) {
            String fieldsBlock = fieldMatcher.group(1);
            // 用正则逐行提取字段定义，避免转义问题
            Pattern linePattern = Pattern.compile("^\\s*([A-Z0-9_]+)\\s+([A-Z0-9_]+)(\\(([^)]+)\\))?.*?(not null)?", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
            Matcher lineMatcher = linePattern.matcher(fieldsBlock);
            while (lineMatcher.find()) {
                String name = lineMatcher.group(1);
                String type = lineMatcher.group(2);
                String length = lineMatcher.group(4);
                TableEntity.Field field = new TableEntity.Field();
                field.setNameSnake(name);
                field.setNameCamel(MrStringUtils.toCamel(name));
                field.setType(type + (length != null ? ("(" + length + ")") : ""));
                field.setCommentCn("");
                field.setCommentEn("");
                field.setDbTyp(type);
                if (length != null) {
                    try { field.setLength(Integer.parseInt(length.split(",")[0].trim())); } catch (Exception ignore) {}
                }
                fields.add(field);
            }
        }
        entity.setFields(fields);
        // 3. 主键
        Pattern pkPattern = Pattern.compile("primary key \\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher pkMatcher = pkPattern.matcher(sql);
        if (pkMatcher.find()) {
            TableEntity.Index pk = new TableEntity.Index();
            pk.setName("PRIMARY");
            String[] pkFields = pkMatcher.group(1).split(",");
            List<String> pkList = new ArrayList<>();
            for (String pkf : pkFields) pkList.add(pkf.trim());
            pk.setFields(pkList);
            entity.setPrimaryKey(pk);
        }
        // 4. 索引
        List<TableEntity.Index> indexes = new ArrayList<>();
        Pattern idxPattern = Pattern.compile("create index ([A-Z0-9_]+)\\s+on\\s+[A-Z0-9_]+\\s*\\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher idxMatcher = idxPattern.matcher(sql);
        while (idxMatcher.find()) {
            TableEntity.Index idx = new TableEntity.Index();
            idx.setName(idxMatcher.group(1));
            String[] idxFields = idxMatcher.group(2).split(",");
            List<String> idxList = new ArrayList<>();
            for (String f : idxFields) idxList.add(f.trim());
            idx.setFields(idxList);
            indexes.add(idx);
        }
        entity.setIndexes(indexes);
        // 5. 字段注释
        Map<String, String> commentMap = new HashMap<>();
        Pattern commentPattern = Pattern.compile("comment on column ([A-Z0-9_]+)\\.([A-Z0-9_]+) is '([^']*)'", Pattern.CASE_INSENSITIVE);
        Matcher commentMatcher = commentPattern.matcher(sql);
        while (commentMatcher.find()) {
            String col = commentMatcher.group(2);
            String comment = commentMatcher.group(3);
            commentMap.put(col, comment);
        }
        for (TableEntity.Field f : fields) {
            if (commentMap.containsKey(f.getNameSnake())) {
                String comment = commentMap.get(f.getNameSnake());
                // 中文和英文用|分隔
                String[] parts = comment.split("\\|");
                f.setCommentCn(parts[0]);
                if (parts.length > 1) f.setCommentEn(parts[1]);
            }
        }
        // 6. 表注释（如有，可扩展）
        return entity;
    }
}
